package internal

import (
	"context"

	"github.com/gogf/gf/v2/database/gdb"
	"github.com/gogf/gf/v2/encoding/gyaml"
	"github.com/gogf/gf/v2/errors/gerror"
	"github.com/gogf/gf/v2/frame/g"
	"github.com/gogf/gf/v2/os/gfile"
)

// InitDB 初始化数据库
func InitDB(ctx context.Context) error {
	// 检查数据库是否已经初始化过
	if isDbInitialized(ctx) {
		g.Log().Info(ctx, "数据库已经初始化过，跳过初始化过程")
		return nil
	}

	g.Log().Info(ctx, "开始初始化数据库...")

	// 确保数据目录存在
	dataDir := "./data"
	if !gfile.Exists(dataDir) {
		if err := gfile.Mkdir(dataDir); err != nil {
			return err
		}
	}

	// 获取数据库连接
	db := g.DB()

	// 执行数据库迁移
	if err := migrate(ctx, db); err != nil {
		return err
	}

	// 初始化系统数据
	if err := initializeSystemData(ctx); err != nil {
		return err
	}

	// 标记数据库已初始化
	if err := markDbAsInitialized(ctx); err != nil {
		g.Log().Warning(ctx, "标记数据库初始化状态失败:", err)
		// 不返回错误，不影响系统启动
	}

	g.Log().Info(ctx, "Database initialized successfully")
	return nil
}

// migrate 执行数据库迁移
func migrate(ctx context.Context, db gdb.DB) error {
	// 创建股票基本信息表
	if err := createStocksTable(ctx, db); err != nil {
		return err
	}

	// 创建股票池相关表
	if err := createStockPoolTables(ctx, db); err != nil {
		return err
	}

	// 创建标签相关表
	if err := createTagTables(ctx, db); err != nil {
		return err
	}

	// 创建系统表
	if err := createSystemTables(ctx, db); err != nil {
		return err
	}

	// 创建策略相关表
	if err := createStrategyTables(ctx, db); err != nil {
		return err
	}

	return nil
}

// createStocksTable 创建股票基本信息表
func createStocksTable(ctx context.Context, db gdb.DB) error {
	sql := `
	CREATE TABLE IF NOT EXISTS stocks (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		code VARCHAR(20) UNIQUE NOT NULL,
		name VARCHAR(100) NOT NULL,
		market VARCHAR(20) NOT NULL DEFAULT '', -- SH/SZ/BJ
		industry VARCHAR(100) DEFAULT '',
		sector VARCHAR(100) DEFAULT '',
		market_cap DECIMAL(15,2) DEFAULT 0,
		pe_ratio DECIMAL(8,2) DEFAULT 0,
		pb_ratio DECIMAL(8,2) DEFAULT 0,
		listing_date DATE,
		is_active BOOLEAN DEFAULT TRUE,
		created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
		updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
	);`

	_, err := db.Exec(ctx, sql)
	if err != nil {
		g.Log().Error(ctx, "Failed to create stocks table:", err)
		return err
	}

	// 创建索引
	indexSqls := []string{
		"CREATE INDEX IF NOT EXISTS idx_stocks_code ON stocks(code);",
		"CREATE INDEX IF NOT EXISTS idx_stocks_market ON stocks(market);",
		"CREATE INDEX IF NOT EXISTS idx_stocks_industry ON stocks(industry);",
		"CREATE INDEX IF NOT EXISTS idx_stocks_active ON stocks(is_active);",
	}

	for _, indexSql := range indexSqls {
		if _, err := db.Exec(ctx, indexSql); err != nil {
			g.Log().Warning(ctx, "Failed to create index:", err)
		}
	}

	g.Log().Info(ctx, "Stocks table created successfully")
	return nil
}

// createStockPoolTables 创建股票池相关表
func createStockPoolTables(ctx context.Context, db gdb.DB) error {
	// 股票池表
	poolSql := `
	CREATE TABLE IF NOT EXISTS stock_pools (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		name VARCHAR(100) NOT NULL,
		description TEXT DEFAULT '',
		type VARCHAR(50) DEFAULT 'static', -- static/dynamic
		max_size INTEGER DEFAULT 100,
		is_active BOOLEAN DEFAULT TRUE,
		created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
		updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
		UNIQUE(name)
	);`

	// 股票池成员表
	memberSql := `
	CREATE TABLE IF NOT EXISTS stock_pool_members (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		pool_id INTEGER NOT NULL,
		stock_code VARCHAR(20) NOT NULL,
		stock_name VARCHAR(100) DEFAULT '',
		weight DECIMAL(5,4) DEFAULT 1.0000,
		added_at DATETIME DEFAULT CURRENT_TIMESTAMP,
		FOREIGN KEY (pool_id) REFERENCES stock_pools(id) ON DELETE CASCADE,
		UNIQUE(pool_id, stock_code)
	);`

	// 动态股票池规则表
	ruleSql := `
	CREATE TABLE IF NOT EXISTS stock_pool_rules (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		pool_id INTEGER NOT NULL,
		rule_name VARCHAR(100) NOT NULL,
		rule_type VARCHAR(50) NOT NULL, -- market_cap/volume/technical/industry
		rule_condition TEXT NOT NULL, -- JSON格式存储条件
		priority INTEGER DEFAULT 1,
		is_active BOOLEAN DEFAULT TRUE,
		created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
		updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
		FOREIGN KEY (pool_id) REFERENCES stock_pools(id) ON DELETE CASCADE
	);`

	sqls := []string{poolSql, memberSql, ruleSql}
	for _, sql := range sqls {
		if _, err := db.Exec(ctx, sql); err != nil {
			g.Log().Error(ctx, "Failed to create stock pool table:", err)
			return err
		}
	}

	// 创建索引
	indexSqls := []string{
		"CREATE INDEX IF NOT EXISTS idx_pool_members_pool_id ON stock_pool_members(pool_id);",
		"CREATE INDEX IF NOT EXISTS idx_pool_members_stock_code ON stock_pool_members(stock_code);",
		"CREATE INDEX IF NOT EXISTS idx_pool_rules_pool_id ON stock_pool_rules(pool_id);",
		"CREATE INDEX IF NOT EXISTS idx_pool_rules_active ON stock_pool_rules(is_active);",
	}

	for _, indexSql := range indexSqls {
		if _, err := db.Exec(ctx, indexSql); err != nil {
			g.Log().Warning(ctx, "Failed to create index:", err)
		}
	}

	g.Log().Info(ctx, "Stock pool tables created successfully")
	return nil
}

// createTagTables 创建标签相关表
func createTagTables(ctx context.Context, db gdb.DB) error {
	// 标签表
	tagSql := `
	CREATE TABLE IF NOT EXISTS tags (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		name VARCHAR(100) NOT NULL,
		category VARCHAR(50) NOT NULL, -- industry/concept/risk/market_cap/custom
		parent_id INTEGER DEFAULT NULL,
		color VARCHAR(20) DEFAULT '#1890ff',
		description TEXT DEFAULT '',
		sort_order INTEGER DEFAULT 0,
		is_active BOOLEAN DEFAULT TRUE,
		created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
		updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
		FOREIGN KEY (parent_id) REFERENCES tags(id) ON DELETE CASCADE,
		UNIQUE(name, category)
	);`

	// 股票标签关联表
	stockTagSql := `
	CREATE TABLE IF NOT EXISTS stock_tags (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		stock_code VARCHAR(20) NOT NULL,
		tag_id INTEGER NOT NULL,
		weight DECIMAL(3,2) DEFAULT 1.00,
		assigned_at DATETIME DEFAULT CURRENT_TIMESTAMP,
		assigned_by VARCHAR(50) DEFAULT 'system',
		FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE,
		UNIQUE(stock_code, tag_id)
	);`

	sqls := []string{tagSql, stockTagSql}
	for _, sql := range sqls {
		if _, err := db.Exec(ctx, sql); err != nil {
			g.Log().Error(ctx, "Failed to create tag table:", err)
			return err
		}
	}

	// 创建索引
	indexSqls := []string{
		"CREATE INDEX IF NOT EXISTS idx_tags_category ON tags(category);",
		"CREATE INDEX IF NOT EXISTS idx_tags_parent_id ON tags(parent_id);",
		"CREATE INDEX IF NOT EXISTS idx_tags_active ON tags(is_active);",
		"CREATE INDEX IF NOT EXISTS idx_stock_tags_stock_code ON stock_tags(stock_code);",
		"CREATE INDEX IF NOT EXISTS idx_stock_tags_tag_id ON stock_tags(tag_id);",
	}

	for _, indexSql := range indexSqls {
		if _, err := db.Exec(ctx, indexSql); err != nil {
			g.Log().Warning(ctx, "Failed to create index:", err)
		}
	}

	g.Log().Info(ctx, "Tag tables created successfully")
	return nil
}

// createSystemTables 创建系统表
func createSystemTables(ctx context.Context, db gdb.DB) error {
	// 用户表
	userSql := `
	CREATE TABLE IF NOT EXISTS users (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		username VARCHAR(50) UNIQUE NOT NULL,
		password VARCHAR(255) NOT NULL,
		email VARCHAR(100) UNIQUE,
		phone VARCHAR(20),
		nickname VARCHAR(100),
		avatar VARCHAR(255),
		role VARCHAR(20) DEFAULT 'user', -- admin/user
		status VARCHAR(20) DEFAULT 'active', -- active/inactive/locked
		last_login_at DATETIME,
		last_login_ip VARCHAR(45),
		login_count INTEGER DEFAULT 0,
		created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
		updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
	);`

	// 用户令牌表
	tokenSql := `
	CREATE TABLE IF NOT EXISTS user_tokens (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		user_id INTEGER NOT NULL,
		token VARCHAR(255) UNIQUE NOT NULL,
		token_type VARCHAR(20) DEFAULT 'access', -- access/refresh
		expires_at DATETIME NOT NULL,
		is_revoked BOOLEAN DEFAULT FALSE,
		created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
		FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
	);`

	// 系统配置表
	configSql := `
	CREATE TABLE IF NOT EXISTS system_config (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		config_key VARCHAR(100) UNIQUE NOT NULL,
		config_value TEXT NOT NULL,
		config_type VARCHAR(20) DEFAULT 'string', -- string/int/float/bool/json
		description TEXT DEFAULT '',
		is_active BOOLEAN DEFAULT TRUE,
		created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
		updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
	);`

	// 系统日志表
	logSql := `
	CREATE TABLE IF NOT EXISTS system_logs (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		level VARCHAR(20) NOT NULL, -- DEBUG/INFO/WARN/ERROR
		module VARCHAR(50) NOT NULL,
		message TEXT NOT NULL,
		context TEXT DEFAULT '', -- JSON格式的上下文信息
		ip_address VARCHAR(45) DEFAULT '',
		user_agent TEXT DEFAULT '',
		created_at DATETIME DEFAULT CURRENT_TIMESTAMP
	);`

	sqls := []string{userSql, tokenSql, configSql, logSql}
	for _, sql := range sqls {
		if _, err := db.Exec(ctx, sql); err != nil {
			g.Log().Error(ctx, "Failed to create system table:", err)
			return err
		}
	}

	// 创建索引
	indexSqls := []string{
		"CREATE INDEX IF NOT EXISTS idx_users_username ON users(username);",
		"CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);",
		"CREATE INDEX IF NOT EXISTS idx_users_status ON users(status);",
		"CREATE INDEX IF NOT EXISTS idx_user_tokens_user_id ON user_tokens(user_id);",
		"CREATE INDEX IF NOT EXISTS idx_user_tokens_token ON user_tokens(token);",
		"CREATE INDEX IF NOT EXISTS idx_user_tokens_expires_at ON user_tokens(expires_at);",
		"CREATE INDEX IF NOT EXISTS idx_system_logs_level ON system_logs(level);",
		"CREATE INDEX IF NOT EXISTS idx_system_logs_module ON system_logs(module);",
		"CREATE INDEX IF NOT EXISTS idx_system_logs_created_at ON system_logs(created_at);",
	}

	for _, indexSql := range indexSqls {
		if _, err := db.Exec(ctx, indexSql); err != nil {
			g.Log().Warning(ctx, "Failed to create index:", err)
		}
	}

	g.Log().Info(ctx, "System tables created successfully")
	return nil
}

// createStrategyTables 创建策略相关表
func createStrategyTables(ctx context.Context, db gdb.DB) error {
	// 策略配置表
	strategySql := `
	CREATE TABLE IF NOT EXISTS strategies (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		name VARCHAR(100) NOT NULL,
		type VARCHAR(50) NOT NULL,
		description TEXT DEFAULT '',
		parameters TEXT DEFAULT '{}', -- JSON格式存储参数
		stock_pool TEXT DEFAULT '[]', -- JSON格式存储股票池
		is_active BOOLEAN DEFAULT FALSE,
		status VARCHAR(20) DEFAULT 'idle', -- idle/running/stopped/error
		start_time DATETIME DEFAULT NULL,
		stop_time DATETIME DEFAULT NULL,
		run_count INTEGER DEFAULT 0,
		error_count INTEGER DEFAULT 0,
		last_error TEXT DEFAULT '',
		created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
		updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
		UNIQUE(name)
	);`

	// 策略执行日志表
	logSql := `
	CREATE TABLE IF NOT EXISTS strategy_logs (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		strategy_id INTEGER NOT NULL,
		level VARCHAR(20) NOT NULL, -- DEBUG/INFO/WARN/ERROR
		message TEXT NOT NULL,
		context TEXT DEFAULT '', -- JSON格式的上下文信息
		created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
		FOREIGN KEY (strategy_id) REFERENCES strategies(id) ON DELETE CASCADE
	);`

	// 交易信号表
	signalSql := `
	CREATE TABLE IF NOT EXISTS trading_signals (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		strategy_id INTEGER NOT NULL,
		stock_code VARCHAR(20) NOT NULL,
		signal_type VARCHAR(10) NOT NULL, -- buy/sell/hold
		price DECIMAL(10,3) NOT NULL,
		quantity INTEGER NOT NULL,
		confidence DECIMAL(3,2) DEFAULT 1.00,
		reason TEXT DEFAULT '',
		extra_data TEXT DEFAULT '{}', -- JSON格式
		status VARCHAR(20) DEFAULT 'pending', -- pending/executed/cancelled
		executed_at DATETIME DEFAULT NULL,
		created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
		FOREIGN KEY (strategy_id) REFERENCES strategies(id) ON DELETE CASCADE
	);`

	// 策略性能表
	performanceSql := `
	CREATE TABLE IF NOT EXISTS strategy_performance (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		strategy_id INTEGER NOT NULL,
		total_return DECIMAL(10,4) DEFAULT 0,
		annual_return DECIMAL(10,4) DEFAULT 0,
		sharpe_ratio DECIMAL(10,4) DEFAULT 0,
		max_drawdown DECIMAL(10,4) DEFAULT 0,
		win_rate DECIMAL(5,4) DEFAULT 0,
		total_trades INTEGER DEFAULT 0,
		win_trades INTEGER DEFAULT 0,
		lose_trades INTEGER DEFAULT 0,
		average_win DECIMAL(10,4) DEFAULT 0,
		average_loss DECIMAL(10,4) DEFAULT 0,
		profit_factor DECIMAL(10,4) DEFAULT 0,
		updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
		FOREIGN KEY (strategy_id) REFERENCES strategies(id) ON DELETE CASCADE,
		UNIQUE(strategy_id)
	);`

	// 持仓表
	positionSql := `
	CREATE TABLE IF NOT EXISTS strategy_positions (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		strategy_id INTEGER NOT NULL,
		stock_code VARCHAR(20) NOT NULL,
		quantity INTEGER NOT NULL,
		avg_price DECIMAL(10,3) NOT NULL,
		current_price DECIMAL(10,3) DEFAULT 0,
		market_value DECIMAL(15,2) DEFAULT 0,
		pnl DECIMAL(15,2) DEFAULT 0,
		pnl_ratio DECIMAL(10,4) DEFAULT 0,
		open_time DATETIME DEFAULT CURRENT_TIMESTAMP,
		last_update DATETIME DEFAULT CURRENT_TIMESTAMP,
		FOREIGN KEY (strategy_id) REFERENCES strategies(id) ON DELETE CASCADE,
		UNIQUE(strategy_id, stock_code)
	);`

	sqls := []string{strategySql, logSql, signalSql, performanceSql, positionSql}
	for _, sql := range sqls {
		if _, err := db.Exec(ctx, sql); err != nil {
			g.Log().Error(ctx, "Failed to create strategy table:", err)
			return err
		}
	}

	// 创建索引
	indexSqls := []string{
		"CREATE INDEX IF NOT EXISTS idx_strategies_type ON strategies(type);",
		"CREATE INDEX IF NOT EXISTS idx_strategies_status ON strategies(status);",
		"CREATE INDEX IF NOT EXISTS idx_strategies_active ON strategies(is_active);",
		"CREATE INDEX IF NOT EXISTS idx_strategy_logs_strategy_id ON strategy_logs(strategy_id);",
		"CREATE INDEX IF NOT EXISTS idx_strategy_logs_level ON strategy_logs(level);",
		"CREATE INDEX IF NOT EXISTS idx_strategy_logs_created_at ON strategy_logs(created_at);",
		"CREATE INDEX IF NOT EXISTS idx_trading_signals_strategy_id ON trading_signals(strategy_id);",
		"CREATE INDEX IF NOT EXISTS idx_trading_signals_stock_code ON trading_signals(stock_code);",
		"CREATE INDEX IF NOT EXISTS idx_trading_signals_status ON trading_signals(status);",
		"CREATE INDEX IF NOT EXISTS idx_trading_signals_created_at ON trading_signals(created_at);",
		"CREATE INDEX IF NOT EXISTS idx_strategy_positions_strategy_id ON strategy_positions(strategy_id);",
		"CREATE INDEX IF NOT EXISTS idx_strategy_positions_stock_code ON strategy_positions(stock_code);",
	}

	for _, indexSql := range indexSqls {
		if _, err := db.Exec(ctx, indexSql); err != nil {
			g.Log().Warning(ctx, "Failed to create index:", err)
		}
	}

	g.Log().Info(ctx, "Strategy tables created successfully")
	return nil
}

// initializeSystemData 初始化系统数据
func initializeSystemData(ctx context.Context) error {
	// 系统数据初始化将在 cmd 层调用 service 层方法来完成
	// 避免在此处直接调用 service 层导致循环依赖
	g.Log().Info(ctx, "System data initialization will be handled by cmd layer")
	return nil
}

// createDefaultAdmin 创建默认管理员账户
func createDefaultAdmin(ctx context.Context) error {
	// 这里不再创建管理员账户，由 service 层统一处理
	// 避免两种不同的加密方式导致的问题
	g.Log().Info(ctx, "Admin account creation delegated to service layer")
	return nil
}

// isDbInitialized 检查数据库是否已经初始化过
func isDbInitialized(ctx context.Context) bool {
	// 从配置文件中检查初始化状态
	initialized := g.Cfg().MustGet(ctx, "database.initialized", false).Bool()
	if initialized {
		return true
	}

	// 如果配置文件中没有设置，检查数据库文件是否存在并且包含表
	dbPath := "./data/goldtree.db"
	if !gfile.Exists(dbPath) {
		return false
	}

	// 检查数据库中是否存在表（以 stocks 表为例）
	db := g.DB()
	// 使用简单的查询检查表是否存在
	_, err := db.Model("stocks").Limit(1).All()
	tableExists := (err == nil)

	// 如果表存在但配置文件中未标记，则更新配置文件
	if tableExists {
		if err := markDbAsInitialized(ctx); err != nil {
			g.Log().Warning(ctx, "更新初始化状态失败:", err)
		}
		return true
	}

	return false
}

// markDbAsInitialized 标记数据库为已初始化
func markDbAsInitialized(ctx context.Context) error {
	// 获取配置文件路径
	configPaths := []string{
		"manifest/config/config.yaml",
		"config/config.yaml",
		"config.yaml",
	}

	var configPath string
	for _, path := range configPaths {
		if gfile.Exists(path) {
			configPath = path
			break
		}
	}

	if configPath == "" {
		return gerror.New("未找到配置文件")
	}

	// 读取当前配置文件
	configContent := gfile.GetContents(configPath)
	if configContent == "" {
		return gerror.New("配置文件为空")
	}

	// 解析YAML配置
	var config map[string]interface{}
	if err := gyaml.DecodeTo([]byte(configContent), &config); err != nil {
		return gerror.Newf("解析配置文件失败: %v", err)
	}

	// 更新数据库初始化状态
	if config["database"] == nil {
		config["database"] = make(map[string]interface{})
	}
	dbConfig := config["database"].(map[string]interface{})
	dbConfig["initialized"] = true

	// 将更新后的配置写回文件
	updatedContent, err := gyaml.Encode(config)
	if err != nil {
		return gerror.Newf("序列化配置失败: %v", err)
	}

	// 备份原配置文件
	backupPath := configPath + ".backup"
	if err := gfile.PutContents(backupPath, configContent); err != nil {
		g.Log().Warning(ctx, "备份配置文件失败:", err)
	}

	// 写入新的配置文件
	if err := gfile.PutContents(configPath, string(updatedContent)); err != nil {
		return gerror.Newf("写入配置文件失败: %v", err)
	}

	g.Log().Info(ctx, "数据库初始化状态已更新")
	return nil
}
